package com.csc.springcore.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.csc.springcore.models.Employee;

public class EmployeeDAO {
	private DataSource db;

	public DataSource getDb() {
		return db;
	}

	public void setDb(DataSource db) {
		this.db = db;
	}

	public Employee findEmployeeById(int id) {
		String sql = "select * from employee where id = ?";
		Connection conn = null;
		try {
			conn = db.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			Employee employee = null;
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				employee = new Employee(rs.getInt("id"), rs.getString("name"),
						rs.getString("gender"), rs.getString("email"));
			}
			rs.close();
			ps.close();
			return employee;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public Employee findEmployeeByName(String name) {
		String sql = "select * from employee where name = ?";
		Connection conn = null;
		try {
			conn = db.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			Employee employee = null;
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				employee = new Employee(rs.getInt("id"), rs.getString("name"),
						rs.getString("gender"), rs.getString("email"));
			}
			rs.close();
			ps.close();
			return employee;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public List<Employee> getEmployeeList() {
		String sql = "select * from employee";
		Connection conn = null;
		try {
			conn = db.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ArrayList<Employee> empList = new ArrayList<Employee>();

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				empList.add(new Employee(rs.getInt("id"), rs.getString("name"),
						rs.getString("gender"), rs.getString("email")));
			}
			rs.close();
			ps.close();
			return empList;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}
}
